<?php
class engagement_mdl extends CI_Model
{
	
	public function get_all($limit, $start)
	{
$this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
articles.nom,
engagements.montant_engagement,
engagements.montant_precedent
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
engagements.annee = $year
LIMIT  $start ,$limit
 ");

return $query->result();
     

	
	}
	
	public function get_all_recherche()
	{

$this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
bcs.id_bc,
fournisseurs.id_fournisseur,
fournisseurs.nom,
engagements.montant_engagement,
engagements.montant_precedent
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
engagements.annee = $year
GROUP BY fournisseurs.id_fournisseur


 ");

return $query->result();
     

	
	}
	
	public function get_all2($limit, $start,$xx)
	{

$this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
articles.nom,
engagements.montant_engagement,
engagements.montant_precedent
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
articles.id_article = $xx and
engagements.annee=$year
LIMIT  $start ,$limit

 ");

return $query->result();
     

	
	}
	
	public function get_per_art_recherche($art)
	{
$this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
fournisseurs.id_fournisseur,
fournisseurs.nom,
engagements.id_article_fk, 
engagements.montant_engagement,
engagements.montant_precedent
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
engagements.id_article_fk = $art and
engagements.annee = $year


 ");

return $query->result();
     

	
	}
	
	public function get_per_fournisseur_recherche($nom_fournisseur)
	{

$this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
fournisseurs.nom,
bcs.id_bc,
engagements.nature
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur

WHERE
fournisseurs.nom LIKE '%$nom_fournisseur%'

 ");

return $query->result();
     

	
	}
	  public function record_count_all() {
	
	$this->load->library('session');
 $year=$this->session->userdata('annee');
   	
      $q = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
articles.nom
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
engagements.annee=$year

");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count($articl_id) {

$this->load->library('session');
 $year=$this->session->userdata('annee');
 	  	
      $q = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
articles.nom
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
articles.id_article = $articl_id and
engagements.annee=$year

");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count_fournisseur($nom) {

$this->load->library('session');
 $year=$this->session->userdata('annee');
 	  	
      $q = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
fournisseurs.nom,
bcs.id_bc,
engagements.nature
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur

WHERE
fournisseurs.nom = '$nom' and
engagements.annee=$year

");

$cont=$q->num_rows();
return $cont;

    }

	public function get_engagement()
	{

$this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
articles.nom
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
engagements.annee=$year


 ");

return $query->result();
     

	
	}
	
		public function get_engagement_detaille($n_engagement)
	{
$this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.date,
engagements.numero,
engagements.nature,
engagements.montant_engagement,
engagements.temps_creation,
engagements.id_article_fk,
articles.nom,
articles.solde,
chapitres.`code` AS code_ch,
articles.`code` AS code_art,
chapitres.nom AS nom_ch,
engagements.annee,
engagements.id_rapport_transfert_fk
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
engagements.id_engagement = $n_engagement


 ");

return $query->result();
     

	
	}
	
		public function get_article_id($id_ch)
	{
$this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
articles.nom,
articles.id_article
FROM
articles
WHERE
articles.id_chapitre_fk = $id_ch


 ");

return $query->result();
     

	
	}
	

	public function existe_budget_sup($id_art)
	{
$this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
engagements.id_engagement,
engagements.numero
FROM
engagements
WHERE
engagements.annee = $year AND
engagements.nature = 2 AND
engagements.numero <> 1 AND
engagements.id_article_fk = $id_art


 ");

return $query->result();
     

	
	}
	
	
	public function get_bcs($n_engagement)
	{

$this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
engagements.id_engagement,
bcs.id_bc,
bc_produits.quantite,
bc_produits.bc_produit_pu,
bcs.date,
bcs.numero,
bcs.id_fournisseur_fk,
fournisseurs.nom
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
LEFT JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
engagements.id_engagement = $n_engagement
GROUP BY bcs.id_bc

 ");

return $query->result();
     

	
	}

    public function get_conventions($n_engagement)
	{

$this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT

engagements.id_article_fk,
conventions.id_convetion,
conventions.date,
conventions.numero,
conventions.is_regularise,
fournisseurs.nom,
fournisseurs.id_fournisseur
FROM
engagements
INNER JOIN conventions ON conventions.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
engagements.id_engagement = $n_engagement


 ");

return $query->result();
     

	
	}
    
	public function verifier_num_enga($nom_article,$num_engagement)
	{

 $annee=$this->session->userdata('annee'); //access it
 
	$query = $this->db->query("SELECT
articles.nom,engagements.numero,engagements.nature,engagements.id_engagement
FROM engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
articles.nom = '$nom_article' AND
engagements.numero = '$num_engagement' AND
engagements.annee = $annee

 ");

return $query->result();
     

	
	}

    public function ajout_engagement($article,$nature,$date,$numero)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
   
   $this->load->library('session');
 $year=$this->session->userdata('annee');
  
	$query = $this->db->query("
INSERT INTO `engagements` (`date`, `numero`, `nature`, `id_article_fk`, `annee`) VALUES ('$date', '$numero', '$nature', '$article' ,'$year')
 ");
	


	}

    public function ajout_engagement3($article,$nature,$date,$numero,$time,$montant,$montant_prec)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
   
   $this->load->library('session');
 $year=$this->session->userdata('annee');
  
	$query = $this->db->query("
INSERT INTO `engagements` (`date`, `numero`, `nature`, `id_article_fk`, `annee`, `temps_creation`, `montant_engagement`, `montant_precedent`) VALUES ('$date', '$numero', '$nature', '$article' ,'$year','$time','$montant','$montant_prec')
 ");
	


	}
	
	 public function ajout_engagement2($article,$nature,$date,$numero,$time)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
   
   $this->load->library('session');
 $year=$this->session->userdata('annee');
  
	$query = $this->db->query("
INSERT INTO `engagements` (`date`, `numero`, `nature`, `id_article_fk`, `annee`, `temps_creation`) VALUES ('$date', '$numero', '$nature', '$article' ,'$year','$time')
 ");
	


	}

     public function ajout_engagement_transfert($article,$nature,$date,$numero,$time,$id_transfert)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
   
   $this->load->library('session');
 $year=$this->session->userdata('annee');
  
	$query = $this->db->query("
INSERT INTO `engagements` (`date`, `numero`, `nature`, `id_article_fk`, `annee`, `temps_creation`, `id_rapport_transfert_fk` ) VALUES ('$date', '$numero', '$nature', '$article' ,'$year','$time','$id_transfert')
 ");
	


	}

     public function mise_a_jour_montant($id_engagement,$montant)
	{

	$query = $this->db->query("
UPDATE `engagements` SET `montant_engagement`='$montant' WHERE (`id_engagement`='$id_engagement')
 ");
	


	}
	
	 public function get_engagement_prec($article)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
		 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
     $query = $this->db->query("SELECT
engagements.numero,
engagements.id_article_fk,
engagements.id_engagement,
engagements.montant_engagement,
engagements.montant_precedent,
engagements.nature
FROM engagements
WHERE
engagements.id_article_fk = '$article' AND
engagements.annee= '$year'
ORDER BY engagements.numero DESC
LIMIT 0,1

 ");
 return $query->result();
}

  	 public function get_engagement_numero($article)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
		 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
     $query = $this->db->query("SELECT
engagements.numero,engagements.id_article_fk,engagements.id_engagement
FROM engagements
WHERE
engagements.id_article_fk = '$article' AND
engagements.annee= '$year'
ORDER BY engagements.numero DESC
LIMIT 0,1

 ");
 return $query->result();
}
     
	 public function get_montant_ancien($article,$t_creation)
	{
		// 1er requete nous donne le dernier numero affecter au engagement de cette article
		 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
     $query = $this->db->query("SELECT
engagements.montant_engagement,
engagements.temps_creation,
engagements.nature
FROM
engagements
WHERE
engagements.id_article_fk = $article AND
engagements.temps_creation < $t_creation AND
engagements.annee = $year


 ");
 return $query->result();
}
   
}


?>